﻿@Imports System.Data
@Imports System.Data.SQLite
@Imports CalibreVBDataSetTableAdapters
@Imports System.Reflection
@Imports System.IO
@Code
    Layout = "~/_SiteLayout.vbhtml"
    
    Dim pagesize As Integer = ConfigurationManager.AppSettings("PageSize").AsInt
    Dim buttonscount As Integer = 10
    Dim currentpageindex As Integer = 0
    Dim pagecount As Double = 0
    Dim _CatRoot() As String = ConfigurationManager.AppSettings("CatRoot").ToString.Split(",")
    Dim _sql_str_where() As String = sql_str_Class.Get_sql_str_where
    Dim _sql_str_key() As String = sql_str_Class.Get_sql_str_key
    Dim _sql_str_tables() As String = sql_str_Class.Get_sql_str_tables
    Dim _sql_str_keyid() As String = sql_str_Class.Get_sql_str_keyid
    
    Dim querystring = Request.ServerVariables("QUERY_STRING")
    Dim pathinfo = Request.ServerVariables("PATH_INFO")

    Dim p As String = Request("p")
    If p = "" Or p = "0" Then
        currentpageindex = 0
    Else
        currentpageindex = Val(p) - 1
    End If

    Dim srt_order_by As String = ""
    Dim LabelSection As String = ""

    Dim cat = UrlData(0).ToString()
    cat = cat.ToLower

    If cat = "" Then
        srt_order_by = " ORDER BY ID DESC"
        cat = "titles"
        LabelSection = cat & " :: What's New"
    End If
    
    Dim itemindex As Int32 = Array.IndexOf(_CatRoot, cat)

    Dim id = ""
    Dim f = ""
    
    If UrlData(1).IsInt Then
        id = UrlData(1).AsInt()
    Else
        f = UrlData(1).ToString()
    End If
   
    Dim sql_str As String = "SELECT books.id, books.title, books.has_cover FROM books " & String.Format(_sql_str_where(itemindex), id) & srt_order_by
    
    Dim tablename As String = _sql_str_tables(itemindex)
    Dim sql_str_key As String = ""
    Dim sql_str_keyid As String = ""

    sql_str_key = _sql_str_key(itemindex)
    sql_str_keyid = _sql_str_keyid(itemindex)
    
    Dim subsection = LibCalibreVB.ExecuteScalar("Select " & sql_str_key & " FROM " & tablename & " WHERE " & sql_str_keyid & " = '" & id & "'").ToLower

    If Not subsection = String.Empty Then
        If cat = "rating" Then
            If subsection = "0" Then
                LabelSection = cat & " :: " & Resources.MainResources.not_yet_rated
            Else
                LabelSection = cat & " :: " & subsection.AsInt / 2
            End If
        Else
            LabelSection = cat & " :: " & subsection
        End If
    Else
        LabelSection = cat
    End If

    If Not f = String.Empty Then
        sql_str = "SELECT books.id, books.title, books.has_cover FROM books " & String.Format(_sql_str_where(itemindex), f)
        LabelSection = cat & " :: " & f.ToLower
    End If
    
    If Not Request.QueryString("q").IsEmpty() Then
        Dim q = Trim(Request.QueryString("q"))
        Dim Str = "SELECT books.id, books.title, books.has_cover FROM books "
        Str = Str & "INNER JOIN authors ON books_authors_link.author = authors.id INNER JOIN books_authors_link ON books.id = books_authors_link.book "
        Str = Str & " WHERE (books.title LIKE '%{0}%' OR authors.name LIKE '%{0}%')"
        sql_str = String.Format(Str, q)
        LabelSection = "search" & " :: " & q.ToLower
    End If

    Dim dataTable As DataTable = LibCalibreVB.GetDataTable(sql_str)

    pagecount = LibCalibreVB.CalculateNumberOfPages(dataTable.Rows.Count, pagesize)

    If currentpageindex + 1 > pagecount Then currentpageindex = pagecount - 1
    If currentpageindex < 0 Then currentpageindex = 0

    sql_str = sql_str & " LIMIT " & pagesize
    sql_str = sql_str & " OFFSET " & currentpageindex * pagesize

    Dim labeltotal = "total recs: " & dataTable.Rows.Count.ToString & ", page count: " & pagecount & ", " & sql_str & ", pag: " & p
    
    Dim db = Database.Open("CalibreDB")
    
    Dim titlelist = db.Query(sql_str).ToList()
    PageData("Title") = LabelSection

End Code

@*@labeltotal*@

<div class="container">
    <div class="row">
        <div class="row"><form class="navbar-form pull-right" method="get">
            <input type="text" class="span3" name="q" value="@Request.QueryString("q")" />
            <button type="submit" class="btn">Search</button>
        </form></div>
        <h2>@LabelSection</h2>

        <div class="pagination pagination-centered">
            @CalibreHelpers.Pagination(pathinfo, pagecount, currentpageindex, buttonscount)
        </div>
        @For Each book In titlelist
            Dim formats = LibCalibreVB.GetFormats(book.id)
            Dim authors = LibCalibreVB.GetAuthors(book.id)
            Dim tags = LibCalibreVB.GetTags(book.id)
            Dim series = LibCalibreVB.GetSeries(book.id)
            Dim rating = LibCalibreVB.GetRating(book.id)
            @<div class="metro">
                <div class="span2">
                    @CalibreHelpers.Formatcover(book.title, book.has_cover, book.id, 1)
                </div>
                <div class="span6">
                    <h3><a href="@Href("~/Details", book.id, LibCalibreVB.StripSlug(book.title))">@book.title</a></h3>
                    @Html.Raw(LibCalibreVB.StripTags(LibCalibreVB.GetComments(book.id)))
                    <br />
                    <br />@CalibreHelpers.FormatTable("Tags", tags, book.id)
                </div>
                <div class="span4">
                    @CalibreHelpers.FormatTileAuthorsSlider("Authors", authors, book.id)
                    @CalibreHelpers.FormatTileFormatsSlider(formats, book.id)
                    @CalibreHelpers.FormatTileSeries("Series", book.id)
                    @CalibreHelpers.FormatTileRating("Rating", book.id)
                    <br />
                    @CalibreHelpers.FormatTileString("test", "~/Tile.ashx", True)
                </div>
            </div>
        Next book
        <div class="pagination pagination-centered">
            @CalibreHelpers.Pagination(pathinfo, pagecount, currentpageindex, buttonscount)
        </div>
    </div>
</div>
